

using System;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Data;
namespace GeneralClasses
{

    ///container class for each department and the stuff inside it

    #region NOTES
    /*
     * 3 NOV 2010 - cleaning up class
13 OCT 2010 - Created class, cant put it in so nothing is tested, still writing in notepadd++.
*/
    #endregion

    /// <summary>
    /// keeps track of everything about the departments for the hospital
    /// </summary>
    public class Department
    {

        #region VARIABLES
        //==============================================================================
        //                                                                     VARIABLES
        //==============================================================================

        private String name;
        private Person managedBy;
        private int peopleNeeded;
        private bool beingEdited;
        private int departmentID;
        private DatabaseConnection dbc;
        private Location location;

        private List<Shift> shifts; // links to all the shifts in this department on that day. (that shift will hold that coresponding day in the datetime class)

        private List<Skill> skills;

        public Schedule schedulePointer;
        #endregion

        #region PROPERTIES
        //==============================================================================
        //                                                                    PROPERTIES
        //==============================================================================

        public Schedule SchedulePointer
        {
            get { return schedulePointer; }
            set { schedulePointer = value; }
        }

        public Location Location
        {
            get { return location; }
            set { location = value; }
        }

        public List<Shift> Shifts
        {
            get { return shifts; }
            set { shifts = value; }
        }

        public List<Skill> SkillsNeeded
        {
            get { return skills; }
            set { skills = value; }
        }

        /*public String ConnectionString
        {
            get
            {
                return dbc.ConnectionString;
            }
            set { dbc.ConnectionString = value; }
        }*/

        public int DepartmentID
        {
            get { return departmentID; }
            set { departmentID = value; }
        }

        public int PeopleNeeded
        {
            get { return peopleNeeded; }
            set { peopleNeeded = value; }
        }

        public Person Managedby
        {
            get { return managedBy; }
            set { managedBy = value; }
        }

        public String Name
        {
            get { return name; }
            set { name = value; }
        }

        #endregion

        #region CONSTRUCTOR
        //==============================================================================
        //                                                                   CONSTRUCTOR
        //==============================================================================

        public Department()
        {
            name = "department";
            departmentID = -1;
            managedBy = new Person();
            peopleNeeded = 1;
            beingEdited = false;
            dbc = new DatabaseConnection();
            skills = new List<Skill>();
            shifts = new List<Shift>();
            location = new Location();

        }

        public Department(Department other)
        {
            name = other.name;
            departmentID = other.departmentID;
            managedBy = other.managedBy;
            peopleNeeded = other.peopleNeeded;
            beingEdited = other.beingEdited;
            dbc = other.dbc;
            skills = other.skills;
            shifts = new List<Shift>();
            location = other.location;

            foreach (Shift shift in other.Shifts)
                shifts.Add(shift);
        }

        #endregion

        #region PRIVATE

        //==============================================================================
        //                                                                       PRIVATE
        //==============================================================================

        private int removeDepartmentRelations()
        {
            try
            {
                if (departmentID != -1)
                {
                    dbc.executeNonQuery("DELETE FROM personhasdepartmentrelationship WHERE deid='" + departmentID + "'");
                    dbc.executeNonQuery("DELETE FROM personhaspreferreddepartmentrelationship WHERE deid='" + departmentID + "'");
                    dbc.executeNonQuery("DELETE FROM departmentmanagedbypersonrelationship WHERE deid='" + departmentID + "'");
                    dbc.executeNonQuery("DELETE FROM shiftinsidedepartmentrelationship WHERE deid='" + departmentID + "'");
                    dbc.executeNonQuery("DELETE FROM departmenthaslocationrelationship WHERE deid='" + departmentID + "'");
                    dbc.executeNonQuery("DELETE FROM departmentneedskillrelationship WHERE deid='" + departmentID + "'");
                    return 0;
                } return -1;
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }
        }


        /// <summary>
        /// returns whether or not this department is being used in a relationship table somewhere. 
        /// (removal must be done in the GUI)
        /// </summary>
        /// <returns></returns>
        private bool checkDepartmentUsed()
        {
            try
            {
                DataTable table = dbc.query("SELECT * FROM personhasdepartmentrelationship WHERE deid='" + departmentID + "'");
                if ((table != null) && table.Rows.Count > 0) { return true; }

                table = dbc.query("SELECT * FROM personhaspreferreddepartmentrelationship WHERE deid='" + departmentID + "'");
                if ((table != null) && table.Rows.Count > 0) { return true; }

                table = dbc.query("SELECT * FROM departmentmanagedbypersonrelationship WHERE deid='" + departmentID + "'");
                if ((table != null) && table.Rows.Count > 0) { return true; }

                table = dbc.query("SELECT * FROM shiftinsidedepartmentrelationship WHERE deid='" + departmentID + "'");
                if ((table != null) && table.Rows.Count > 0) { return true; }

                table = dbc.query("SELECT * FROM departmenthaslocationrelationship WHERE deid='" + departmentID + "'");
                if ((table != null) && table.Rows.Count > 0) { return true; }

                table = dbc.query("SELECT * FROM departmentneedskillrelationship WHERE deid='" + departmentID + "'");
                if ((table != null) && table.Rows.Count > 0) { return true; }

                return false;
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }
        }

        /// <summary>
        /// returns whether or not this department exists in the database
        /// </summary>
        /// <returns></returns>
        private bool checkDepartmentExists()
        {
            try
            {
                DataTable table = dbc.query("SELECT deid FROM Department WHERE name = '" + name + "'");
                if ((table != null) && table.Rows.Count > 0)
                {
                    return true;
                }
                return false;
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }
        }

        private int findDepartmentID()
        {
            try
            {
                DataTable table = dbc.query("SELECT deid FROM Department WHERE name = '" + name + "'");
                if ((table != null) && table.Rows.Count > 0)
                {
                    foreach (DataRow row in table.Rows)
                    {
                        return (int)row["deid"];
                    }
                }
                return -1;
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }
        }

        #endregion

        #region PUBLIC
        //==============================================================================
        //                                                                        PUBLIC
        //==============================================================================

        public int writeOutObjectTree(int uniqueCounter, int sepCounter, int spaceIndex, DateTime day)
        {
            String spaces = "";
            Shift oldShift = null;
            if (shifts.Count > 0)
            {
                oldShift = shifts[0];
            }
            int shiftCounter = 0;

            for (int i = 0; i < spaceIndex; i++) { spaces = spaces + "\t"; }
            UTIL.sayDebug("(" + uniqueCounter + ")" + spaces + "(" + sepCounter + ")" + this.GetType().Name);

            foreach (Shift shift in this.shiftsOnThisDay(day))
            {
                if (!shift.Equals(oldShift))
                {
                    uniqueCounter++;
                    oldShift = shift;
                    shiftCounter++;
                }
                uniqueCounter = shift.writeOutObjectTree(uniqueCounter, shiftCounter, spaceIndex + 1);
            }

            return uniqueCounter;
        }


        public List<Shift> shiftsOnThisDay(DateTime day)
        {
            int dayNum = day.Day;
            List<Shift> list = new List<Shift>();

            //foreach (Shift shift in this.shifts)
            for (int i = 0; i < shifts.Count; i++)
            {
                if ((shifts[i].StartTime.Day == dayNum) || (shifts[i].EndTime.Day == dayNum))
                {
                    list.Add(shifts[i]);
                }
            }
            return list;
        }

        public void setWithIndex(Person person, DateTime day, int shiftIndex)
        {
            shiftsOnThisDay(day)[shiftIndex].setWithIndex(person);
        }

        /// <summary>
        /// removes anything not related to the given person
        /// </summary>
        /// <param name="person">person you want to relate this department to</param>
        public void personalize(Person person)
        {
            foreach (Shift s in shifts)
            {
                s.personalize(person);
            }

            for (int i = 0; i <= shifts.Count - 1; i++)
            {
                if (shifts[i].Persons.Count == 0)
                {
                    shifts.RemoveAt(i);
                }
            }
        }

        /// <summary>
        /// returns a list of all departments in the database
        /// </summary>
        /// <returns></returns>
        public static List<Department> loadAllDepartments()
        {

            try
            {
                DatabaseConnection dbc = new DatabaseConnection();
                List<Department> departments = new List<Department>();
                DataTable table = dbc.query("SELECT deid FROM Department");
                if ((table != null) && table.Rows.Count > 0)
                {
                    foreach (DataRow row in table.Rows)
                    {
                        Department department = new Department();
                        department.DepartmentID = (int)row["deid"]; // assign the personID
                        //department.schedulePointer = schedulePointer;
                        //department.ConnectionString = dbc.ConnectionString; // default one
                        department.loadData();
                        departments.Add(department);
                    }
                }
                return departments;
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }
        }

        /// <summary>
        /// returns the total number of departments in the database
        /// </summary>
        public static int getDepartmentCount()
        {
            try
            {
                DatabaseConnection dbc = new DatabaseConnection();
                DataTable table = dbc.query("SELECT COUNT(deid) AS numrows FROM Department");
                if ((table != null) && table.Rows.Count > 0)
                {
                    return (int)table.Rows[0][0];
                }
                return -1;
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }
        }

        /// <summary>
        /// returns a list of all the skills in this department, with redundant ones taken out
        /// </summary>
        /// <param name="persons"></param>
        /// <returns></returns>
        public List<Skill> getAllPersonelSkills()
        {
            List<Skill> skills = new List<Skill>();
            List<Person> persons = loadPeopleInside(); // get all the people in this department

            //put all the skills into one list
            foreach (Person p in persons)
            {
                //skills.AddRange(p.Skills);
            }

            //remove redundant skills
            for (int i = 0; i <= skills.Count - 2; i++)
            {
                for (int q = i + 1; q <= skills.Count - 1; q++)
                {
                    if (skills[i].Equals(skills[q]))
                    {
                        skills.RemoveAt(q);
                    }
                }
            }
            return skills;
        }

        /// <summary>
        /// gets all the shifts set to work in this department in that day
        /// </summary>
        /// <returns></returns>
        public List<Shift> loadShifts()
        {
            try
            {
                List<Shift> shiftList = new List<Shift>();

                DataTable table = dbc.query("SELECT shid FROM shiftinsidedepartment WHERE deid = '" + this.departmentID + "'");
                if ((table != null) && table.Rows.Count > 0)
                {
                    foreach (DataRow row in table.Rows)
                    {
                        Shift shift = new Shift();
                        shift.ShiftID = (int)row["shid"];
                        shift.loadData();
                        shiftList.Add(shift);
                    }
                }

                return shiftList;
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }
        }

        /// <summary>
        /// returns a list of all the people that work in this department that are still active
        /// </summary>
        /// <returns></returns>
        public List<Person> loadPeopleInside()
        {
            try
            {
                List<Person> persons = new List<Person>();

                DataTable table = dbc.query("SELECT perid FROM personhasdepartmentrelationship D WHERE D.deid ='" + departmentID + "' AND active='1'");
                if ((table != null) && table.Rows.Count > 0)
                {
                    foreach (DataRow row in table.Rows)
                    {
                        Person person = new Person();
                        //person.ConnectionString = this.ConnectionString;
                        person.PERID = (int)row["perid"];
                        person.loadData();
                        persons.Add(person);
                    }
                }
                return persons;
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }
        }

        /// <summary>
        /// saves all the data in this department class onto the database except relationship tables
        /// </summary>
        public int saveDataSimple()
        {
            try
            {
                if (departmentID == -1)
                {
                    if (!checkDepartmentExists())
                    {
                        //add
                        dbc.executeNonQuery("INSERT INTO Department (name) values ('" + this.name + "')");
                    }
                    departmentID = findDepartmentID();
                }
                else
                {
                    dbc.executeNonQuery("UPDATE Department SET name='" + name + "' WHERE deid='" + departmentID + "'");
                }

                return departmentID;
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }
        }

        /// <summary>
        /// saves all the data in this department class onto the database
        /// </summary>
        public int saveData()
        {
            try
            {
                if (departmentID == -1)
                {
                    if (!checkDepartmentExists())
                    {
                        //add
                        dbc.executeNonQuery("INSERT INTO Department (name) values ('" + this.name + "')");
                    }
                    departmentID = findDepartmentID();
                }
                else
                {
                    dbc.executeNonQuery("UPDATE Department SET name='" + name + "' WHERE deid='" + departmentID + "'");
                }

                //------------------------------------------
                if (location.LID != -1)
                {
                    //remove old department->location relationship data
                    dbc.executeNonQuery("DELETE FROM departmenthaslocationrelationship WHERE deid = '" + this.departmentID + "' AND lid = '" + location.LID + "'");
                }

                //save location
                int lid = location.saveData();

                //save department->location relationship
                dbc.executeNonQuery("INSERT INTO departmenthaslocationrelationship (deid, lid) values ('" + this.departmentID + "','" + lid + "')");
                //------------------------------------------

                //------------------------------------------
                foreach (Skill skill in skills)
                {
                    if (skill.SkillID != -1)
                    {
                        //remove old relationship
                        dbc.executeNonQuery("DELETE FROM departmentneedskillrelationship WHERE deid = '" + this.departmentID + "' AND sid = '" + skill.SkillID + "'");
                    }

                    //save new skill
                    int sid = skill.saveData();

                    //add in new relationship
                    dbc.executeNonQuery("INSERT INTO departmentneedskillrelationship (deid, sid) values ('" + departmentID + "','" + sid + "')");
                }
                //------------------------------------------


                //------------------------------------------
                if (shifts.Count > 0)
                {
                    //remove old relationships
                    dbc.executeNonQuery("DELETE FROM shiftinsidedepartmentrelationship WHERE deid = '" + this.departmentID + "'");
                }
                foreach (Shift shift in shifts)
                {
                    if (shift.ShiftID != -1)
                    {
                        //save new skill
                        int shid = shift.saveData();

                        //add in new relationship
                        dbc.executeNonQuery("INSERT INTO shiftinsidedepartmentrelationship (deid, shid) values ('" + departmentID + "','" + shid + "')");
                    }
                }
                //------------------------------------------


                return departmentID;
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }
        }

        public static Department loadData(int deid)
        {
            Department department = new Department();
            department.DepartmentID = deid;
            department.loadData();

            return department;
        }

        //loads all the data that it needs
        public void loadData()
        {
            try
            {
                //load name
                DataTable table = dbc.query("SELECT name FROM Department WHERE deid='" + departmentID + "'");
                if ((table != null) && table.Rows.Count > 0)
                {
                    foreach (DataRow row in table.Rows)
                    {
                        name = (String)row["name"];
                    }
                }


                //load managedby
                table = dbc.query("SELECT perid FROM departmentmanagedbypersonrelationship WHERE deid='" + this.departmentID + "'");
                if ((table != null) && table.Rows.Count > 0)
                {
                    foreach (DataRow row in table.Rows)
                    {
                        ///this function is a public call to the container class Schedule, in which it tells it to load this person,
                        ///add it to the master list of persons, and then return it back to us so we have a local pointer

                        if (schedulePointer != null) // if this is in a schedule, then pass it up top
                        {
                            this.managedBy = schedulePointer.loadPersonToMasterList((int)row["perid"]);
                        }
                        else
                        {
                            Person person = new Person();
                            person.PERID = (int)row["perid"];
                            person.loadData();
                            this.managedBy = person;
                        }
                    }
                }

                //load people needed; // well, it holds the list of titles it needs, but no general number right now.

                //load beingEdited // another thing that needs to be done with a new table, i guess. we will worry about this later

                //load location
                table = dbc.query("SELECT lid FROM departmenthaslocationrelationship WHERE deid = '" + departmentID + "'");
                if ((table != null) && table.Rows.Count > 0)
                {
                    foreach (DataRow row in table.Rows)
                    {
                        location.LID = (int)row["lid"];
                        location.loadData();
                    }
                }

                //--------------------------------------------
                table = dbc.query("SELECT sid FROM departmentneedskillrelationship WHERE deid='" + this.departmentID + "'");
                if ((table != null) && (table.Rows.Count > 0))
                {
                    foreach (DataRow row in table.Rows)
                    {
                        Skill skill = new Skill();
                        skill.SkillID = (int)row["sid"];
                        skill.loadData();
                        skills.Add(skill);
                    }
                }
                //--------------------------------------------



                //--------------------------------------------
                // load all teh shifts inside this departemnt, pass to master list, and get them back for the local list
                table = dbc.query("SELECT shid FROM shiftinsidedepartmentrelationship WHERE deid='" + this.departmentID + "'");
                if ((table != null) && table.Rows.Count > 0)
                {
                    foreach (DataRow row in table.Rows)
                    {
                        //passes shift ID to schedule.
                        // schedule loads that shift and adds to master list
                        // schedule returns that shift to be added in this department's local list.
                        if (schedulePointer != null)
                        {
                            shifts.Add(schedulePointer.loadShiftToMasterList((int)row["shid"]));
                        }
                        else
                        {
                            //old method
                            Shift shift = new Shift();
                            shift.ShiftID = (int)row["shid"];
                            shift.loadData();
                            shifts.Add(shift);
                        }
                    }
                }
                //--------------------------------------------
                return;

            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }
        }

        /// <summary>
        /// removes all the data for this department
        /// </summary>
        /// <returns></returns>
        public int removeData()
        {
            #region deprecated
            //if (location.removeData() == -1) { return -1; } // deprecated

            /*foreach (Shift s in shifts)
            {
                if (s.removeData() == -1) { return -1; }
            }

            foreach (Skill s in skills)
            {
                if (s.removeData() == -1) { return -1; }
            }*/
            #endregion

            try
            {
                if (this.departmentID != -1)
                {
                    removeDepartmentRelations();
                    if (!checkDepartmentUsed())
                    {
                        dbc.executeNonQuery("DELETE FROM Department WHERE deid='" + departmentID + "'");
                        return 0;
                    }
                }
                return -1;
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }
        }

        //these need to be worked on later
        public bool StartEditing()
        {
            //needs persmissions passed to it and needs to judge them and the currently edited ID
            if (beingEdited) return false;
            else { beingEdited = true; return true; }
        }
        public void stopEditing()
        {
            //needs permiussions with this
            beingEdited = false;
        }

        public void deprecated_deleteDepartment() { }
        public void deprecated_addDepartment() { }

        #endregion

    } // end class


} //end namespace
